package com.yu.newsSys.dao.impl;

import com.yu.newsSys.dao.NewsDao;
import com.yu.newsSys.domain.News;
import com.yu.newsSys.utils.CommonUtils;
import com.yu.newsSys.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl implements NewsDao {
    @Override
    public List<News> getNews(String newsPaperName, String fileTitle, Integer start, Integer pageSize) {

        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        List<News> list = new ArrayList<>();

        try {
            conn = JDBCUtils.getConnection();
            // 拼接sql语句
            String sql = "select * from news where 1=1";

            if (newsPaperName != null && !newsPaperName.equals("")) {
                sql += " and newsName like concat('%',?,'%')";
            }
            if (fileTitle != null && !fileTitle.equals("")) {
                sql += " and newsTitle like concat('%',?,'%')";
            }
            // 拼接分页
            sql += " limit ?,?";


            prep = conn.prepareStatement(sql);

            int i = 1;
            if (newsPaperName != null && !newsPaperName.equals("")) {
                prep.setString(i++, newsPaperName);
            }
            if (fileTitle != null && !fileTitle.equals("")) {
                prep.setString(i++, fileTitle);
            }
            // 若不给定起始页 默认为1 而pageSize也必定有值 因此不必判断start和pageSize是否为空，直接拼接即可
            prep.setInt(i++, start);
            prep.setInt(i++, pageSize);
            System.out.println(sql);
            rs = prep.executeQuery();
            // 遍历结果集
            while (rs.next()) {
                int id = rs.getInt("id");
                Date newsDate = rs.getDate("newsDate");
                String newsName = rs.getString("newsName");
                String layoutName = rs.getString("layoutName");
                String newsAuthor = rs.getString("newsAuthor");
                String newsTitle = rs.getString("newsTitle");
                String newsInfo = rs.getString("newsInfo");
                String newsContent = rs.getString("newsContent");
                News news = new News(id,newsDate,newsName,layoutName,newsAuthor,newsTitle,newsInfo,newsContent);
                list.add(news);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,prep,conn);
        }
        // 打印日志
        CommonUtils.logInfo("getNews","dao",list);
        return list;
    }

    @Override
    public int add(News news) {
        CommonUtils.logInfo("add","dao",news);
        Connection conn = null;
        PreparedStatement prep = null;
        int count = 0;

        try {
            conn = JDBCUtils.getConnection();
            prep = conn.prepareStatement("insert into news (newsDate,newsName,layoutName,newsAuthor,newsTitle,newsInfo,newsContent)" +
                    "values " + "(?,?,?,?,?,?,?)");

            prep.setDate(1, (Date) news.getNewsDate());
            prep.setString(2,news.getNewsName());
            prep.setString(3,news.getLayoutName());
            prep.setString(4,news.getNewsAuthor());
            prep.setString(5,news.getNewsTitle());
            prep.setString(6, news.getNewsInfo());
            prep.setString(7,news.getNewsContent());

            count = prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null,prep,conn);
        }
        return count;
    }

    @Override
    public News getNewsById(int id) {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        News news = null;

        CommonUtils.logInfo("getNewsById","dao",id);
        try {
            conn = JDBCUtils.getConnection();
            prep = conn.prepareStatement("select * from news where id =?");
            prep.setInt(1,id);
            rs = prep.executeQuery();

            if (rs.next()) {
                Date newsDate = rs.getDate("newsDate");
                String newsName = rs.getString("newsName");
                String layoutName = rs.getString("layoutName");
                String newsAuthor = rs.getString("newsAuthor");
                String newsTitle = rs.getString("newsTitle");
                String newsInfo = rs.getString("newsInfo");
                String newsContent = rs.getString("newsContent");
                news = new News(id,newsDate,newsName,layoutName,newsAuthor,newsTitle,newsInfo,newsContent);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(rs,prep,conn);
        }
        CommonUtils.logInfo("getNewsById","dao",news);
        return news;
    }

    @Override
    public int update(News news) {
        CommonUtils.logInfo("update","dao",news);
        Connection conn = null;
        PreparedStatement prep = null;
        int count = 0;

        try {
            conn = JDBCUtils.getConnection();
            prep = conn.prepareStatement("update news set newsDate =?,newsName = ?,layoutName=?,newsAuthor=?,newsTitle=?,newsInfo=?,newsContent=? where id = ?");

            prep.setDate(1, (Date) news.getNewsDate());
            prep.setString(2,news.getNewsName());
            prep.setString(3,news.getLayoutName());
            prep.setString(4,news.getNewsAuthor());
            prep.setString(5,news.getNewsTitle());
            prep.setString(6, news.getNewsInfo());
            prep.setString(7,news.getNewsContent());
            prep.setInt(8,news.getId());

            count = prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null,prep,conn);
        }
        return count;
    }

    @Override
    public int delete(int id) {
        CommonUtils.logInfo("delete","dao",id);
        Connection conn = null;
        PreparedStatement prep = null;
        int count = 0;
        try {
            conn = JDBCUtils.getConnection();
            prep = conn.prepareStatement("delete from news where id = ?");
            prep.setInt(1,id);
            count = prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null,prep,conn);
        }
        return count;
    }

    @Override
    public Integer getTotal(String newsPaperName, String fileTitle) {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = JDBCUtils.getConnection();
            // 拼接sql语句
            String sql = "select count(id) from news where 1=1";

            if (newsPaperName != null && !newsPaperName.equals("")) {
                sql += " and newsName like concat('%',?,'%')";
            }
            if (fileTitle != null && !fileTitle.equals("")) {
                sql += " and newsTitle like concat('%',?,'%')";
            }

            prep = conn.prepareStatement(sql);

            int i = 1;
            if (newsPaperName != null && !newsPaperName.equals("")) {
                prep.setString(i++, newsPaperName);
            }
            if (fileTitle != null && !fileTitle.equals("")) {
                prep.setString(i++, fileTitle);
            }
            rs = prep.executeQuery();
            System.out.println(sql);
            // 遍历结果集
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,prep,conn);
        }
        return count;
    }

}
